package com.classes;

import java.util.ArrayList;
import java.util.TreeMap;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Blob;

import org.json.simple.*;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;

public class CasemastFactory {
	private static String message;

	public static String getMessage() {
		return "[CasemastFactory] " + CasemastFactory.message;
	}

	public static String getStartStatus(int branch){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "select * from statusmast "
					+ "where s_current = '_START_' "
					+ "and s_branch = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, branch);

				ResultSet resultSet = statement.executeQuery();

				try {
					String next = "";
					String sep = "";
					while(resultSet.next()){
						next += sep + resultSet.getString("s_next");
						sep = ",";
					}


					return next;
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}



		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return null;
	}

	public static ArrayList<String> getStatusFilter(int branch){
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		String startStatus = CasemastFactory.getStartStatus(branch);
		if(startStatus == null){
			return null;
		}

		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";

		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}

		try {
			String sql = "select distinct s_next from statusmast "
					+ "where s_next not in (" + paramStr + ", ?) "
					+ "and s_branch = ? "
					+ "order by s_next asc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				int paramIndex = 1;
				for(String s : startArr) {
					statement.setString(paramIndex++, s);
				}

				statement.setString(paramIndex++, "*");
				statement.setInt(paramIndex++, branch);

				ResultSet resultSet = statement.executeQuery();

				try {
					ArrayList<String> next = new ArrayList<String>();
					while(resultSet.next()){
						next.add(resultSet.getString("s_next"));
					}

					return next;
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			e.printStackTrace();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return null;
	}

	public static ArrayList<Casemast> getUnattendedCasemasts(){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();

		try {
			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "where c_status_date < sysdate - 90 ";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static ArrayList<Casemast> getPendingCasemasts(String groupCode, TreeMap<String, String> search) {
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();

		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}

		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";

		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}

		try {
			String q_search = "";

			if(search != null) {
				if(search.containsKey("case_id") && search.get("case_id").length() > 0){
					q_search += "and c_reference_no = ? ";
				}

				if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
					q_search += "and upper(c_merchant_details) like ? ";
				}

				if(search.containsKey("group_code") && search.get("group_code").length() > 0){
					q_search += "and c_group_code = ? ";
				}

				if(search.containsKey("group_user") && search.get("group_user").length() > 0){
					q_search += "and c_created_by = ? ";
				}

				if(search.containsKey("application_type") && search.get("application_type").length() > 0){
					q_search += "and c_type = ? ";
				}

				if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
					q_search += "and c_reseller_location = ? ";
				}
			}

			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "left join tbltree on child_id = c_group_code "
					+ "where parent_id = ? and c_status in (" + paramStr + ") "
					+ q_search
					+ "order by c_id asc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, groupCode);

				int last_index = 1;
				for(String s : startArr) {
					statement.setString(++last_index, s);
				}

				if(search != null) {
					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("case_id").toUpperCase());
					}

					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						++last_index;
						statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
					}

					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("group_code"));
					}

					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("group_user"));
					}

					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("application_type"));
					}

					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("reseller_location"));
					}
				}

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static ArrayList<Casemast> getActiveCasemasts(String groupCode, TreeMap<String, String> search) {
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();

		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}

		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";

		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}

		try {
			String q_search = "";

			if(search != null) {
				if(search.containsKey("case_id") && search.get("case_id").length() > 0){
					q_search += "and c_reference_no = ? ";
				}

				if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
					q_search += "and upper(c_merchant_details) like ? ";
				}

				if(search.containsKey("group_code") && search.get("group_code").length() > 0){
					q_search += "and c_group_code = ? ";
				}

				if(search.containsKey("group_user") && search.get("group_user").length() > 0){
					q_search += "and c_created_by = ? ";
				}

				if(search.containsKey("application_type") && search.get("application_type").length() > 0){
					q_search += "and c_type = ? ";
				}

				if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
					q_search += "and c_reseller_location = ? ";
				}

				if(search.containsKey("status") && search.get("status").length() > 0){
					q_search += "and c_status = ? ";
				}
			}

			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "left join tbltree on child_id = c_group_code "
					+ "where parent_id = ? "
					+ "and c_status not in (" + paramStr + ") "
					+ "and c_status not like 'CLOSE%' "
					+ q_search
					+ "order by c_id desc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, groupCode);

				int last_index = 1;
				for(String s : startArr) {
					statement.setString(++last_index, s);
				}

				if(search != null) {

					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("case_id").toUpperCase());
					}

					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						++last_index;
						statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
					}

					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("group_code"));
					}

					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("group_user"));
					}

					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("application_type"));
					}

					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("reseller_location"));
					}

					if(search.containsKey("status") && search.get("status").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("status"));
					}
				}

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static ArrayList<Casemast> getCasemasts(String groupCode, TreeMap<String, String> search) {
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();

		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}

		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";

		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}

		try {
			String q_search = "";

			if(search != null) {
				if(search.containsKey("status") && search.get("status").length() > 0){
					q_search += "and c_status = ? ";
				}

				if(search.containsKey("case_id") && search.get("case_id").length() > 0){
					q_search += "and c_reference_no = ? ";
				}

				if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
					q_search += "and upper(c_merchant_details) like ? ";
				}

				if(search.containsKey("group_code") && search.get("group_code").length() > 0){
					q_search += "and c_group_code = ? ";
				}

				if(search.containsKey("group_user") && search.get("group_user").length() > 0){
					q_search += "and c_created_by = ? ";
				}

				if(search.containsKey("application_type") && search.get("application_type").length() > 0){
					q_search += "and c_type = ? ";
				}

				if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
					q_search += "and c_submit_date >=  to_date(?, 'YYYY-MM-DD') and c_submit_date < to_date(?, 'YYYY-MM-DD') + 1 ";
				}

				if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
					q_search += "and c_reseller_location = ? ";
				}
			}

			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "left join tbltree on child_id = c_group_code "
					+ "where parent_id = ? and c_status not in (" + paramStr + ") "
					+ q_search
					+ (q_search.isEmpty() ? "and rownum <= 1000 " : "")
					+ "order by c_id desc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, groupCode);

				int last_index = 1;
				for(String s : startArr) {
					statement.setString(++last_index, s);
				}

				if(search != null) {
					if(search.containsKey("status") && search.get("status").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("status"));
					}

					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("case_id").toUpperCase());
					}

					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						++last_index;
						statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
					}

					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("group_code"));
					}

					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("group_user"));
					}

					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("application_type"));
					}

					if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("submit_date"));

						++last_index;
						statement.setString(last_index, search.get("submit_date"));
					}

					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("reseller_location"));
					}
				}

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static ArrayList<TreeMap<String, String>> getPendingVisaVMTSCasemasts() {
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();

		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}

		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";

		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}

		try {
			String sql = "select casemast.*, caseoutlet.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "left join caseoutlet on co_case_id = c_id "
					+ "where c_visa_vmts_batch is null "
					+ "and c_status not in (" + paramStr + ", 'CLOSE') "
					+ "order by c_id asc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				int last_index = 1;
				for(String s : startArr) {
					statement.setString(last_index++, s);
				}

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						TreeMap<String, String> c = CasemastFactory.parseOutletResultSet(resultSet);
						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			e.printStackTrace();
			CasemastFactory.message = "*** " + e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static Casemast getCaseMast(String groupCode, long id){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "left join tbltree on child_id = c_group_code "
					+ "where parent_id = ? and c_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, groupCode);
				statement.setLong(2, id);
				
				System.out.println("sql"+sql);
				System.out.println("groupCode"+groupCode);
				System.out.println("id"+id);

				ResultSet resultSet = statement.executeQuery();

				try {

					if(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						return c;
					}
					else {
						CasemastFactory.message = "Application not found.";
						return null;
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}


		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			e.printStackTrace();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return null;
	}

	public static Casemast getCaseMast(long id){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "where c_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setLong(1, id);
				
				System.out.println("sql2"+sql);
				System.out.println("id"+id);

				ResultSet resultSet = statement.executeQuery();

				try {
					if(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						return c;
					}
					else {
						CasemastFactory.message = "Application not found.";
						return null;
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}


		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return null;
	}

	public static Casemast getCaseMastByRefNo(String ref_no){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "select casemast.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "where c_reference_no = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, ref_no);
				
				System.out.println("sql3"+sql);
				System.out.println("ref_no"+ref_no);

				ResultSet resultSet = statement.executeQuery();

				try {
					if(resultSet.next()){
						Casemast c = CasemastFactory.parseResultSet(resultSet);
						return c;
					}
					else {
						CasemastFactory.message = "Application not found.";
						return null;
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}


		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return null;
	}

	protected static Casemast parseResultSet(ResultSet resultSet) throws Exception{
		JSONObject merchant_json = (JSONObject)JSONValue.parse(resultSet.getString("c_merchant_details"));
		JSONArray avg_deposit_json = (JSONArray) merchant_json.get("curr_avg_deposit_dtl");

		Merchant m = new Merchant();
		m.setId(Long.parseLong(resultSet.getString("c_merchant_id")));

		m.setOwnershipStatus(merchant_json.get("ownership_status").toString());
		m.setRegisteredName(merchant_json.get("registered_name").toString());
		m.setRegisteredNo(merchant_json.get("registered_no").toString());
		m.setDesc(merchant_json.get("desc").toString());
		m.setMCCCode(merchant_json.get("mcc_code").toString());
		m.setPaidUpCapital(merchant_json.get("paid_up_capital").toString());
		m.setRecallBarcode(merchant_json.get("recall_barcode").toString());
		m.setGst_registration(merchant_json.containsKey("gst_registration") ? merchant_json.get("gst_registration").toString() : null);
		m.setRmcd_approval_no(merchant_json.containsKey("rmcd_approval_no") ? merchant_json.get("rmcd_approval_no").toString() : null);
		m.setYearInBusiness(merchant_json.get("year_in_business").toString());
		m.setAvgTicketSize(merchant_json.get("avg_ticket_size").toString());
		m.setCurrBank(merchant_json.get("curr_bank").toString());
		m.setCurrAccNo(merchant_json.get("curr_acc_no").toString());
		m.setCurrAvgDeposit(merchant_json.get("curr_avg_deposit").toString());
		m.setCurrAvgDepositDtl(avg_deposit_json.toString());
		m.setAmexAuthSign(resultSet.getString("c_amex_auth_sign"));
		m.setMatchTrace(resultSet.getString("c_match_trace"));

		Casemast c = new Casemast();

		c.setId(Long.parseLong(resultSet.getString("c_id")));
		c.setGroupCode(resultSet.getString("c_group_code"));
		c.setGroupName(resultSet.getString("role_name"));
		c.setAction(resultSet.getString("c_action"));
		c.setStatus(resultSet.getString("c_status"));
		c.setAction2(resultSet.getString("c_action2"));
		c.setStatus2(resultSet.getString("c_status2"));
		c.setBranch2(resultSet.getString("c_branch2"));
		c.setType(resultSet.getString("c_type"));
		c.setAcquirer(resultSet.getString("c_acquirer"));
		c.setMerchant(m);
		c.setCreatedDate(((Timestamp) resultSet.getTimestamp("c_created_date")).getTime());
		c.setCreatedBy(resultSet.getString("c_created_by"));

		if(resultSet.getTimestamp("c_submit_date") != null){
			c.setSubmitDate(((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime());
		}

		c.setReferenceNo(resultSet.getString("c_reference_no"));
		c.setLastReferenceNo(resultSet.getString("c_last_reference_no"));
		c.setCountryCode(resultSet.getString("c_country_code"));
		c.setDocumentChecklist(resultSet.getString("c_document_checklist"));
		c.setExternalRemark(resultSet.getString("c_ext_remark"));
		c.setExternalRefNo(resultSet.getString("c_ext_ref_no"));
		c.setResellerLocation(resultSet.getString("c_reseller_location"));
		c.setExternalCode(resultSet.getString("c_ext_code"));
		c.setExportedExternalCode(resultSet.getString("c_export_ext_code"));

		return c;
	}

	protected static TreeMap<String, String> parseOutletResultSet(ResultSet resultSet) throws Exception{
		JSONObject merchant_json = (JSONObject)JSONValue.parse(resultSet.getString("c_merchant_details"));

		TreeMap<String, String> c = new TreeMap<String, String>();

		c.put("id", resultSet.getString("c_id"));
		c.put("merchant_id", resultSet.getString("c_merchant_id"));
		c.put("reference_no", resultSet.getString("c_reference_no"));
		c.put("country_code", resultSet.getString("c_country_code"));
		c.put("registered_name", merchant_json.get("registered_name").toString());
		c.put("registered_no", merchant_json.get("registered_no").toString());
		c.put("mcc_code", merchant_json.get("mcc_code").toString());
		c.put("match_trace", resultSet.getString("c_match_trace"));

		c.put("outlet_id", "");
		c.put("trading_name", "");
		c.put("address", "");
		c.put("email", "");
		c.put("tel_no", "");

		JSONObject outlet_obj = new JSONObject();
		if(resultSet.getString("co_outlet_details") != null){
			outlet_obj = (JSONObject) JSONValue.parse(resultSet.getString("co_outlet_details"));

			c.put("outlet_id", resultSet.getString("co_outlet_id"));
			c.put("trading_name", outlet_obj.get("trading_name").toString());
			c.put("address", resultSet.getString("co_address"));
			c.put("email", outlet_obj.get("email").toString());
			c.put("tel_no", outlet_obj.get("telephone_no").toString());
		}



		return c;
	}

	public static long action_submit(int case_id, TreeMap<String, String> post, String user, int branch){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "{? = call STATUSACTION.action_next(?, ?, ?, ?, ?, ?)}";

			CallableStatement cstmt = conn.prepareCall(sql);

			try {
				cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
	            cstmt.setInt(2, case_id);
				cstmt.setString(3, post.get("action"));
				cstmt.setString(4, post.get("note"));
				cstmt.setString(5, post.get("assign_to"));
				cstmt.setString(6, user);
				cstmt.setInt(7, branch);

	            cstmt.executeQuery();

	            long rtn = cstmt.getLong(1);


				if(rtn > 0){
					return rtn;
				} else {
					CasemastFactory.message = "Error occured while executing STATUSACTION.action_next.";
					return -1;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				cstmt.close();
			}

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return -1;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return -1;
	}

	public static boolean extremark_submit(int case_id, TreeMap<String, String> post, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "update casemast set c_ext_remark = ?, c_modified_date = sysdate, c_modified_by = ? where c_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, post.get("external_remark"));
				statement.setString(2, user);
				statement.setLong(3, case_id);

	            statement.execute();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static boolean document_submit(int case_id, TreeMap<String, String> post, byte[] data, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "insert into casedoc(cd_case_id, cd_name, cd_data, cd_type, cd_length, cd_ref_no, cd_created_by, cd_created_date) "
				+ "values(?, ?, empty_blob(), ?, ?, ?, ?, sysdate)";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setLong(1, case_id);
				statement.setString(2, post.get("file_name"));
				statement.setString(3, post.get("file_type"));
				statement.setLong(4, Long.parseLong(post.get("file_size")));
				statement.setString(5, post.get("ref_no").length() > 0 ? post.get("ref_no") : null);
				statement.setString(6, user);

	            statement.execute();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}


			Long doc_id = null;
			ResultSet rs;
			Statement statement2 = conn.createStatement();

			try {
				rs = statement2.executeQuery("SELECT SEQ_CASEDOC.CURRVAL FROM DUAL");

				try {
					if (rs.next()) {
						doc_id = new Long(rs.getLong(1));
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement2.close();
			}


			sql = "select cd_data from casedoc where cd_id = ? for update";

			statement = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

			try {
				statement.setLong(1, doc_id);

				rs = statement.executeQuery();

				try {
					if (rs.next()) {
						Blob blob = rs.getBlob("cd_data");
						blob.truncate(0);
						blob.setBytes(1, data);
						rs.updateBlob("cd_data", blob);
						rs.updateRow();
					}

					return true;
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
		return false;
	}

	public static boolean document_delete(int doc_id, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "delete from casedoc where cd_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setLong(1, doc_id);
	            statement.execute();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}


	public static boolean visa_vmts_batch_submit(String case_json_arr, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			JSONArray arr = (JSONArray) JSONValue.parse(case_json_arr);
			if(arr.size() <= 0){
				return true;
			}

			String sql = "insert into visavmtsbatch(b_created_by, b_created_date) "
				+ "values(?, sysdate)";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, user);
	            statement.execute();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}


			Long batch_id = null;
			Statement statement2 = conn.createStatement();

			try {
				ResultSet rs = statement2.executeQuery("SELECT SEQ_VISAVMTSBATCH.CURRVAL FROM DUAL");
				try {
					if (rs.next()) {
						batch_id = new Long(rs.getLong(1));
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					rs.close();
				}
			} finally {
				statement2.close();
			}



			sql = "update casemast set c_visa_vmts_batch = ? where c_id = ?";

			statement = conn.prepareStatement(sql);

			try {
				for(int i=0; i<arr.size(); i++){
					long case_id = Long.parseLong(arr.get(i).toString());

					statement.setLong(1, batch_id);
					statement.setLong(2, case_id);
					statement.execute();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}


			return true;

		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public static ArrayList<TreeMap<String, String>> getVisaVMTSBatch(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);

		try {
			String q_search = "";

			if(search != null) {
				if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
					q_search += "where b_created_date >=  to_date(?, 'YYYY-MM-DD') and b_created_date < to_date(?, 'YYYY-MM-DD') + 1 ";
				}
			}


			String sql = "select * from visavmtsbatch "
					+ q_search
					+ "order by b_id desc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				if(search != null) {
					int last_index = 0;

					if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
						++last_index;
						statement.setString(last_index, search.get("submit_date"));

						++last_index;
						statement.setString(last_index, search.get("submit_date"));
					}
				}

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						TreeMap<String, String> c = new TreeMap<String, String>();
						c.put("id", resultSet.getString("b_id"));
						c.put("created_date", resultSet.getString("b_created_date"));
						c.put("created_by", resultSet.getString("b_created_by"));

						Long t = resultSet.getTimestamp("b_created_date").getTime();
						c.put("created_date", t.toString());

						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			e.printStackTrace();
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}


	public static ArrayList<TreeMap<String, String>> getVisaVMTSBatchDetails(long batch_id) {
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();

		try {
			String sql = "select casemast.*, caseoutlet.*, visavmtsbatch.*, role_name from casemast "
					+ "left join tblaccessrole on c_group_code = role_code "
					+ "left join caseoutlet on co_case_id = c_id "
					+ "left join visavmtsbatch on c_visa_vmts_batch = b_id "
					+ "where c_visa_vmts_batch = ? "
					+ "order by c_id asc";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setLong(1, batch_id);

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						TreeMap<String, String> c = CasemastFactory.parseOutletResultSet(resultSet);

						c.put("batch_id", resultSet.getString("b_id"));
						c.put("batch_created_date", resultSet.getString("b_created_date"));
						c.put("batch_created_by", resultSet.getString("b_created_by"));

						Long t = resultSet.getTimestamp("b_created_date").getTime();
						c.put("batch_created_date", t.toString());

						cArr.add(c);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					resultSet.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				statement.close();
			}

			return cArr;

		} catch (Exception e){
			e.printStackTrace();
			CasemastFactory.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}
}